package code14JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author 黄衡熙
 * @version 1.0
 */
/*t_dept部门、t_emp员工表
 *id=dept_id
 * 实现保存部门与员工信息
 * 要求同时保存指定的某个部门以及该部门下的三位员工*/
public class Test07_部门员工表 {
    public static void main(String[] args) {
        Dept dept = new Dept("后勤部","负责后勤事务");
        System.out.println("保存前"+dept);
        Integer id=addDept(dept);
        dept.setId(id);
        System.out.println(dept);
        Emp emp=new Emp("admin",5500.0,dept.getId());
        Emp emp2=new Emp("jack",6500.0,dept.getId());
        Emp emp3=new Emp("lucy",7500.0,dept.getId());
        addEmp(emp);
        addEmp(emp2);
        addEmp(emp3);

    }

    public static Integer addDept(Dept dept) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Integer id = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_dept (name,remark) ")
                    .append(" values(?,?) ")
                    .toString();
            ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1, dept.getName());
            ps.setString(2, dept.getRemark());
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            while (rs.next()) {
                id = rs.getInt(1);
            }
            return id;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
    }

    public static void addEmp(Emp emp) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" insert into t_emp(name, salary, dept_id) ")
                    .append(" values(?, ?, ?) ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, emp.getName());
            ps.setDouble(2, emp.getSalary());
            ps.setInt(3,emp.getDept_id());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(conn, ps, null);
        }

    }
}
